import os
import shutil 
import win32com.client

from string import Template

from DataAccess.DBConnFactory import DBConnFactory

from Config.ImportConfig import *

def Import_Index_Weight(dir_path = IDX_WEIGHT_READ_DIR_PATH):
	
	if not os.path.isdir(dir_path):
		raise RuntimeError(), "Failed to open the dir " + dir_path
	
	xApp = win32com.client.Dispatch("Excel.Application")
	conn = DBConnFactory().get_db_connection('FINCHINA')
	
	sql_tpl_cls = Template('''merge into springs_index_weight sp
							using (select TO_DATE('${DATE}','yyyymmdd') as ref_date,
										'${IDX_TICK}' as index_ticker,
										'${SEC_TICK}' as sec_ticker,
										'${CLS}' as close,
										'${W}' as weight
									from dual) t
							on (sp.ref_date=t.ref_date and sp.index_ticker=t.index_ticker and sp.sec_ticker=t.sec_ticker)
							when matched then update set
								sp.close=t.close,
								sp.weight=t.weight
							when not matched then insert (sp.ref_date,sp.index_ticker,sp.sec_ticker,sp.close,sp.weight)
							values(t.ref_date,t.index_ticker,t.sec_ticker,t.close,t.weight)''')

	sql_tpl_adj = Template('''merge into springs_index_weight sp
							using (select TO_DATE('${DATE}','yyyymmdd') as ref_date,
										'${IDX_TICK}' as index_ticker,
										'${SEC_TICK}' as sec_ticker,
										'${PRE_ADJ_CLS}' as pre_adj_close,
										'${PRE_ADJ_W}' as pre_adj_weight
									from dual) t
							on (sp.ref_date=t.ref_date and sp.index_ticker=t.index_ticker and sp.sec_ticker=t.sec_ticker)
							when matched then update set
								sp.pre_adj_close=t.pre_adj_close,
								sp.pre_adj_weight=t.pre_adj_weight
							when not matched then insert (sp.ref_date,sp.index_ticker,sp.sec_ticker,sp.pre_adj_close,sp.pre_adj_weight)
							values(t.ref_date,t.index_ticker,t.sec_ticker,t.pre_adj_close,t.pre_adj_weight)''')

	for elem in os.listdir(dir_path):
		file_prefix = os.path.splitext(elem)[0]
		file_suffix = os.path.splitext(elem)[1]
		abs_path = os.path.join(dir_path, elem)
		
		if file_prefix[9:].lower() == 'spc300_nc_adj' and file_suffix.lower() == '.spc':
		
			xBook = xApp.Workbooks.Open(abs_path)
			xSht = xBook.Worksheets(file_prefix)
			
			for i in range(7, 307):
				ref_date = int(xSht.Cells(i, 2).Value)
				index_ticker = '816000'
				sec_ticker = xSht.Cells(i, 4).Value[:6]
				pre_adj_close = xSht.Cells(i, 16).Value
				pre_adj_weight = xSht.Cells(i, 25).Value
				
				sql_text = sql_tpl_adj.substitute(DATE = ref_date,
												IDX_TICK = index_ticker,
												SEC_TICK = sec_ticker,
												PRE_ADJ_CLS = pre_adj_close,
												PRE_ADJ_W = pre_adj_weight)
				conn.cursor().execute(sql_text)
		
		elif file_prefix[9:].lower() == 'spc300_nc_cls' and file_suffix.lower() == '.spc':
		
			xBook = xApp.Workbooks.Open(abs_path)
			xSht = xBook.Worksheets(file_prefix)

			for i in range(7, 307):
				ref_date = int(xSht.Cells(i, 2).Value)
				index_ticker = '816000'
				sec_ticker = xSht.Cells(i, 4).Value[:6]
				close = xSht.Cells(i, 16).Value
				weight = xSht.Cells(i, 25).Value
				
				sql_text = sql_tpl_cls.substitute(DATE = ref_date,
												IDX_TICK = index_ticker,
												SEC_TICK = sec_ticker,
												CLS = close,
												W = weight)
				conn.cursor().execute(sql_text)
		
		else:
			print 'warning: file is not index weight file ' + elem
			continue
		
		xBook.Close()
		shutil.copy2(abs_path, IDX_WEIGHT_BACKUP_DIR_PATH)
		os.remove(abs_path)
		
		print 'running info, the file', file_prefix, 'has been imported successfully.'
	
	conn.commit()
	del xApp

if __name__ == '__main__':

	Import_Index_Weight()